﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class LichVanChuyen_DAO
    {
        SqlConnection cn = DataProvider.ConnectDB();
        
        public DataTable XemLichVanChuyen_TheoTuyenDuong(int matuyenduong, ErrorDTO error)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("sp_XemLichVanChuyen_theotuyenduong", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@matuyenduong", matuyenduong));

            SqlParameter output = cmd.Parameters.Add(new SqlParameter("@error", SqlDbType.NVarChar, 100));
            output.Direction = ParameterDirection.Output;

            SqlParameter output1 = cmd.Parameters.Add(new SqlParameter("@slxe", SqlDbType.Int));
            output1.Direction = ParameterDirection.Output;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            error.Msg = output.Value.ToString();
            error.slxe = Convert.ToInt32(output1.Value);

            cn.Close();
            return dt;

        }

        public DataTable XemLichVanChuyen_tatca(ErrorDTO error)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("sp_XemLichVanChuyen_tatca", cn);
            cmd.CommandType = CommandType.StoredProcedure;
           
            SqlParameter output = cmd.Parameters.Add(new SqlParameter("@error", SqlDbType.NVarChar, 100));
            output.Direction = ParameterDirection.Output;

            SqlParameter output1 = cmd.Parameters.Add(new SqlParameter("@slxe", SqlDbType.Int));
            output1.Direction = ParameterDirection.Output;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            error.Msg = output.Value.ToString();
            error.slxe = Convert.ToInt32(output1.Value);
            cn.Close();
            return dt;

        }

        public DataTable XemLicgVanChuyen_theothoigian(DateTime ngaydi,DateTime ngayden ,ErrorDTO error)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("sp_XemLichVanChuen_theothoigian", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@ngaydi", ngaydi));
            cmd.Parameters.Add(new SqlParameter("@ngayden", ngayden));

            SqlParameter output = cmd.Parameters.Add(new SqlParameter("@error", SqlDbType.NVarChar, 100));
            output.Direction = ParameterDirection.Output;

            SqlParameter output1 = cmd.Parameters.Add(new SqlParameter("@slxe", SqlDbType.Int));
            output1.Direction = ParameterDirection.Output;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            error.Msg = output.Value.ToString();
            error.slxe = Convert.ToInt32(output1.Value);
            cn.Close();
            return dt;
        }

        /// <summary>
        /// /////////////
        /// </summary>
        /// <returns></returns>
        public DataSet LichLamViecAll()
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand("sp_LichLamViecAll", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);

            cn.Close();
            return ds;
        }
        //??
        public DataTable layDanhSachXeCua1Lich(int mavc)
        {
            DataTable dt = new DataTable();

            //SqlConnection cn = DataProvider.ConnectDB();
            //string str = "Select Ma_VanChuyen ,Ma_Xe ,[So_TaiXe] ,[So_PhuLai] ,[So_TaiXeTrong] ,[So_PhuLaiTrong]   from ChiTietLich where Ma_VanChuyen = " + mavc;
            ////string str = "Select Ma_VanChuyen as 'Mã vận chuyển',Ma_Xe as 'Mã Xe',[So_TaiXe] as 'Số tài xế',[So_PhuLai] as 'Số phụ lái',[So_TaiXeTrong] as 'Số tài xế trống',[So_PhuLaiTrong] as 'Số phụ lái trống'   from ChiTietLich where Ma_VanChuyen = "+ mavc;
            //SqlDataAdapter da = new SqlDataAdapter(str, cn);
            //da.Fill(dt);
            //cn.Close();

            SqlCommand cmd = new SqlCommand("sp_layDanhSachXeCua1Lich", cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@mavanchuyen", mavc));

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            return dt;
        }

        public DataTable layTuyenDuong()
        {
            DataTable dt = new DataTable();

            SqlConnection cn = DataProvider.ConnectDB();

            string str = "Select Ma_TuyenDuong,Diem_XuatPhat + '-' +Diem_Den as TuyenDuong from TuyenDuong  ";
            SqlDataAdapter da = new SqlDataAdapter(str, cn);
            da.Fill(dt);
            cn.Close();

            return dt;
        }

        public DataTable layDanhSachXe()
        {
            DataTable dt = new DataTable();

            SqlConnection cn = DataProvider.ConnectDB();

            string str = "Select *  from Xe ";
            SqlDataAdapter da = new SqlDataAdapter(str, cn);
            da.Fill(dt);
            cn.Close();

            return dt;
        }


        public DataTable layDanhSachXeRanh(DateTime dt1)
        {

            DataTable dt = new DataTable();

            SqlCommand cmd = new SqlCommand("sp_layDanhSachXeRanh", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@giodi", dt1));
            SqlDataAdapter da = new SqlDataAdapter(cmd);

            //SqlConnection cn = DataProvider.ConnectDB();
            //string str = "Select *  from Xe Where Ma_Xe not in(select Ma_Xe from LichVanChuyen lvc, ChiTietLich ctl Where lvc.Ma_VanChuyen = ctl.Ma_VanChuyen and Ngay_Di != #" + dt1 + "# ) ";
            //SqlDataAdapter da = new SqlDataAdapter(str, cn);
           
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        public int themLichMoi(Lichvanchuyen_DTO lvcDTO)
        {
            SqlCommand cmd = new SqlCommand("sp_ThemLichMoi", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@giodi", lvcDTO.Gio_di));
            cmd.Parameters.Add(new SqlParameter("@gioden", lvcDTO.Gio_den));
            
            cmd.Parameters.Add(new SqlParameter("@idtuyenduong", lvcDTO.Ma_tuyenduong));
            cmd.Parameters.Add(new SqlParameter("@tinhtrang", lvcDTO.Tinhtrang));
      

            int kq = cmd.ExecuteNonQuery();
            cn.Close();
            if (kq >= 0)
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }

        public int capNhatLich(Lichvanchuyen_DTO lvcDTO)
        {
            SqlCommand cmd = new SqlCommand("sp_CapNhatLich", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@malich", lvcDTO.Ma_vanchuyen));
            cmd.Parameters.Add(new SqlParameter("@giodi", lvcDTO.Gio_di));
            cmd.Parameters.Add(new SqlParameter("@gioden", lvcDTO.Gio_den));
            cmd.Parameters.Add(new SqlParameter("@idtuyenduong", lvcDTO.Ma_tuyenduong));
            cmd.Parameters.Add(new SqlParameter("@tinhtrang", lvcDTO.Tinhtrang));

            int kq = cmd.ExecuteNonQuery();
            cn.Close();
            if (kq >= 0)
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }

        public void XoaLich(int maLich)
        {
            SqlCommand cmd = new SqlCommand("sp_XoaLich", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@malich", maLich));
            int kq = cmd.ExecuteNonQuery();
            cn.Close();

        }

        public void capNhatXeVaoLich(int loaitt, Chitietlich_DTO ctlDTO)
        {
            //string str = "Select Ma_VanChuyen ,Ma_Xe ,[So_TaiXe] ,[So_PhuLai] ,[So_TaiXeTrong] ,[So_PhuLaiTrong]   from ChiTietLich where Ma_VanChuyen = " + mavc;
            SqlCommand cmd = new SqlCommand("sp_ThemXeVaoLich", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@loai", loaitt));
            cmd.Parameters.Add(new SqlParameter("@malich", ctlDTO.Ma_vanchuyen));
            cmd.Parameters.Add(new SqlParameter("@maxe", ctlDTO.Ma_xe));
            cmd.Parameters.Add(new SqlParameter("@taixe", ctlDTO.So_taixe));
            cmd.Parameters.Add(new SqlParameter("@phulai", ctlDTO.So_phulai));
            cmd.Parameters.Add(new SqlParameter("@taixetrong", ctlDTO.So_taixetrong));
            cmd.Parameters.Add(new SqlParameter("@phulaitrong", ctlDTO.So_phulaitrong));
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public void xoaChiTietLich(int maLich)
        {
            //string str = "Select Ma_VanChuyen ,Ma_Xe ,[So_TaiXe] ,[So_PhuLai] ,[So_TaiXeTrong] ,[So_PhuLaiTrong]   from ChiTietLich where Ma_VanChuyen = " + mavc;
            SqlCommand cmd = new SqlCommand("sp_XoaChiTietLich", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@malich", maLich));
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public void capNhatTaiXePhuLaiTrong(int maLich)
        {
            //string str = "Select Ma_VanChuyen ,Ma_Xe ,[So_TaiXe] ,[So_PhuLai] ,[So_TaiXeTrong] ,[So_PhuLaiTrong]   from ChiTietLich where Ma_VanChuyen = " + mavc;
            SqlCommand cmd = new SqlCommand("sp_CapNhatTaiXePhuLaiTrong", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@malich", maLich));
            cmd.ExecuteNonQuery();
            cn.Close();
        }
    }


}
